import time,re
import pandas as pd
import sys
import redis
import pickle
import logging
import hashlib
import configparser
import datetime
from sqlalchemy import create_engine, DateTime, String,text
import pymysql
import requests
import os
import pywencai
import akshare as ak
import pandas as pd
from datetime import datetime,timedelta
import traceback
import time,re
import pandas as pd
import sys
import redis
import pickle
import logging
import hashlib
import configparser
import datetime
from sqlalchemy import create_engine, DateTime, String,text
import pymysql
import requests
import os
import pywencai
import akshare as ak
import pandas as pd
from datetime import datetime,timedelta
import traceback
pymysql.install_as_MySQLdb()

os.environ['PATH'] = '/home/chencan/node/bin:$PATH";'
log_format = "%(asctime)s - %(levelname)s - %(filename)s:%(lineno)d - %(message)s"
date_format = "%Y-%m-%d %H:%M:%S"  # 精确到秒
logging.basicConfig(level=logging.DEBUG, format=log_format, datefmt=date_format)

# 日志文件路径
log_file_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'application.log')

# 创建一个 handler，用于写入日志文件
file_handler = logging.FileHandler(log_file_path)
file_handler.setFormatter(logging.Formatter(log_format, date_format))
# 添加 handler 到 logger
logging.getLogger().addHandler(file_handler)

# 初始化配置解析器
config = configparser.ConfigParser()
current_dir = os.path.dirname(os.path.abspath(__file__))
config.read(current_dir+'/config.ini', encoding='utf-8')


# 获取Redis的配置信息
redis_host = config.get('Redis', 'host')
redis_port = config.getint('Redis', 'port')
redis_db = config.getint('Redis', 'db')
redis_password = config.get('Redis', 'password')
r = redis.Redis(host=redis_host, port=redis_port, db=redis_db, password=redis_password)

mysql_port = config.getint('mysql', 'port')
mysql_host = config.get('mysql', 'host')
mysql_db = config.get('mysql', 'db')
import urllib.parse
mysql_password = urllib.parse.quote(config.get('mysql', 'password'))
mysql_user = config.get('mysql', 'user')
db_url = f'mysql://{mysql_user}:{mysql_password}@{mysql_host}:{mysql_port}/{mysql_db}'

engine = create_engine(db_url,pool_size=20,max_overflow=20,pool_recycle=60)



def get_next_trade_date(trade_date):
    tool_trade_date_hist_sina_df = ak.tool_trade_date_hist_sina()
    # 筛选出所有晚于给定交易日的日期
    date_df = tool_trade_date_hist_sina_df[tool_trade_date_hist_sina_df["trade_date"] > pd.Timestamp(trade_date).date()]
    date_df = date_df.sort_values(by="trade_date", ascending=True)  # 按日期升序排序
    next_trade_date = date_df["trade_date"].values[0]  # 获取最接近给定日期的下一个交易日
    return next_trade_date.strftime("%Y%m%d")  # 格式化日期

# 定义获取前一个交易日的函数
def get_pre_trade_date(trade_date):
    tool_trade_date_hist_sina_df = ak.tool_trade_date_hist_sina()
    date_df = tool_trade_date_hist_sina_df[tool_trade_date_hist_sina_df["trade_date"] < pd.Timestamp(trade_date).date()]
    date_df = date_df.sort_values(by="trade_date", ascending=False)
    pre_trade_date = date_df["trade_date"].values[0]
    return pre_trade_date.strftime("%Y%m%d")

def get_pre_trade_date_n(trade_date,n):
    tool_trade_date_hist_sina_df = ak.tool_trade_date_hist_sina()
    date_df = tool_trade_date_hist_sina_df[tool_trade_date_hist_sina_df["trade_date"] < pd.Timestamp(trade_date).date()]
    date_df = date_df.sort_values(by="trade_date", ascending=False)
    pre_trade_date = date_df["trade_date"].values[n-1]
    return pre_trade_date.strftime("%Y%m%d")


#记录前一天的数据
def PanQian(query_date):
    pre_trade_date = get_pre_trade_date(query_date)
    next_trade_date = get_next_trade_date(query_date)
    last_year_date = get_pre_trade_date_n(query_date,200)
    last_60_date = get_pre_trade_date_n(query_date, 60)

    question =f'''
        {pre_trade_date}涨跌幅，概念，行业，{pre_trade_date}连续涨停天数，{pre_trade_date}市值,{pre_trade_date}成交额,{pre_trade_date}的收盘价,
        {pre_trade_date}的5日均线,{pre_trade_date}的10日均线,{pre_trade_date}的20日均线,或者{last_year_date}到{pre_trade_date}的涨停次数，或者{last_60_date}到{pre_trade_date}的涨停次数
    '''

    logging.info(f"问句:{question}")
    df = pywencai.get(query=question,loop=True)
    logging.info(f"获得的列名:{df.columns}")
    df["股票代码"] = df["股票代码"].str[0:6]


    df.rename({f"连续涨停天数[{pre_trade_date}]": "连续涨停天数"}, axis=1, inplace=True)
    df.rename({f"涨跌幅:前复权[{pre_trade_date}]":"涨跌幅"}, axis=1, inplace=True)
    df.rename({f"a股市值(不含限售股)[{pre_trade_date}]":"流通市值"}, axis=1, inplace=True)
    df.rename({f"收盘价:不复权[{pre_trade_date}]":"收盘价"}, axis=1, inplace=True)
    df.rename({f"成交额[{pre_trade_date}]":"成交额"}, axis=1, inplace=True)
    df.rename({f"5日均线[{pre_trade_date}]": "5日均线"}, axis=1, inplace=True)
    df.rename({f"10日均线[{pre_trade_date}]": "10日均线"}, axis=1, inplace=True)
    df.rename({f"20日均线[{pre_trade_date}]": "20日均线"}, axis=1, inplace=True)
    df.rename({f"涨停次数[{last_year_date}-{pre_trade_date}]": "近一年涨停次数"}, axis=1, inplace=True)
    df.rename({f"涨停次数[{last_60_date}-{pre_trade_date}]": "近一个季度涨停次数"}, axis=1, inplace=True)

    df['连续涨停天数'] = df['连续涨停天数'].fillna(0)
    df["流通市值"]  = df["流通市值"].astype(float)
    df["流通市值"] = round(df["流通市值"]/100000000,2)
    df["成交额"]  = df["成交额"].astype(float)
    df["成交额"] = round(df["成交额"]/100000000,2)
    df["涨跌幅"]  = df["涨跌幅"].astype(float)
    df["收盘价"] = df["收盘价"].astype(float)

    # 新股可能没有均线
    df['5日均线'] = df['5日均线'].replace('', 1)
    df["5日均线"] = df["5日均线"].astype(float)
    df['10日均线'] = df['10日均线'].replace('', 1)
    df["10日均线"] = df["10日均线"].astype(float)
    df['20日均线'] = df['20日均线'].replace('', 1)
    df["20日均线"] = df["20日均线"].astype(float)


    df["5日乖离"] = round((df["收盘价"]-df["5日均线"])*100/df["5日均线"],2)
    df["10日乖离"] = round((df["收盘价"] - df["10日均线"]) * 100 / df["10日均线"], 2)
    df["20日乖离"] = round((df["收盘价"] - df["20日均线"]) * 100 / df["20日均线"], 2)

    df = df[["股票代码","股票简称","所属概念","所属同花顺行业","连续涨停天数","涨跌幅","流通市值","收盘价","成交额","5日乖离","10日乖离","20日乖离","近一个季度涨停次数","近一年涨停次数"]]

    #将涨停次数这一列的空值填充为0
    df['近一年涨停次数'] = df['近一年涨停次数'].fillna(0)
    df['近一年涨停次数'] = df['近一个季度涨停次数'].fillna(0)
    df["日期"] = query_date
    r.set(f"stock_panqian:{query_date}", pickle.dumps(df))
    r.expire(f"stock_panqian:{query_date}", 60*60*24*2)

    with engine.connect() as connection:
        sql = f"DELETE FROM stock_fupan WHERE 日期='{query_date}'"
        print(sql)
        delete_statement = text(sql)
        result = connection.execute(delete_statement)
        connection.commit()
        print(result.rowcount, "rows deleted.")
        df.to_sql("stock_fupan",engine, if_exists='append', index=False)

    return df

#查看负反馈是否强烈
def QingXu(query_date):
    question = f"{query_date}竞价跌幅前50,{query_date}涨跌幅，非北交所，非ST，非科创，上市时间大于365天"
    print(question)
    df = pywencai.get(question=question)

    df = df.rename(columns={f'涨跌幅:前复权[{query_date}]': '涨跌幅', f'分时涨跌幅:前复权[{query_date} 09:25]': '竞价'})

    df = df.head(10)
    df["竞价"]  = df["竞价"].astype(float)
    df["竞价"] = round(df["竞价"],2)
    df = df[["股票代码", "股票简称", "竞价"]]
    return df


def getZhaBan(query_date):
    # 获取前两个交易日
    pre_trade_date = get_pre_trade_date(query_date)
    pre_2_trade_date = get_pre_trade_date(pre_trade_date)

    # 构造查询问句
    question = f'''
    {pre_2_trade_date}涨停,非北交所,{pre_trade_date}长上影或者炸板或者收阴线，非ST，{query_date}集合竞价涨跌幅,{pre_2_trade_date}收盘价,{pre_trade_date}收盘价,
    {pre_trade_date}成交额,{pre_2_trade_date}连续涨停天数,{query_date}竞价成交额,{pre_trade_date}市值，行业，概念
    '''
    print(question)

    # 使用问财获取数据
    df = pywencai.get(question=question)
    # print(df)
    # print(df.columns)

    # 排序并重命名列
    df = df.sort_values(by=f"分时涨跌幅:前复权[{query_date} 09:25]", ascending=False)
    df.rename({f"分时涨跌幅:前复权[{query_date} 09:25]": "竞价涨跌幅"}, axis=1, inplace=True)

    df.rename({f"连续涨停天数[{pre_2_trade_date}]": "连板"}, axis=1, inplace=True)
    df["连板"] = df["连板"].astype(int)

    df["竞价涨跌幅"] = df["竞价涨跌幅"].astype(float)
    df = df.head(10)
    df = df[df["竞价涨跌幅"] > 0]

    df.rename({f"a股市值(不含限售股)[{pre_trade_date}]": "市值"}, axis=1, inplace=True)
    df["市值"] = df["市值"].astype(float)
    df["市值"] = round(df["市值"] / 100000000, 2)

    df["跌幅"] = round((df[f"收盘价:不复权[{pre_trade_date}]"] - df[f"收盘价:不复权[{pre_2_trade_date}]"]) * 100 / df[
        f"收盘价:不复权[{pre_2_trade_date}]"], 2)
    df.rename({f"成交额[{pre_trade_date}]": "昨日成交额"}, axis=1, inplace=True)
    df["昨日成交额"] = df["昨日成交额"].astype(float)
    df["昨日成交额"] = round(df["昨日成交额"] / 100000000, 2)

    df.rename({f"竞价金额[{query_date}]": "竞价金额"}, axis=1, inplace=True)
    df["竞价金额"] = df["竞价金额"].astype(float)
    df["竞价金额"] = round(df["竞价金额"] / 100000000, 2)

    df.rename({"所属同花顺行业": "行业"}, axis=1, inplace=True)

    df = df[df["昨日成交额"] > 5]
    # 添加“类型”列，并筛选显示的列
    df["类型"] = "隔日弱转强"
    df = df[["股票简称", "股票代码", "竞价涨跌幅", "类型", "跌幅", "昨日成交额", "连板", "竞价金额", "市值", "行业",
             "所属概念"]]

    # 返回最终结果
    return df


def getJieLi(query_date):
    # 获取前两个交易日
    pre_trade_date = get_pre_trade_date(query_date)
    pre_2_trade_date = get_pre_trade_date(pre_trade_date)

    # 构造查询问句
    question = f'''
    {pre_trade_date}涨停,非ST，行业，非北交所，{query_date}集合竞价涨跌幅,{pre_trade_date}成交额,{pre_trade_date}连续涨停天数,{query_date}竞价成交额,
    {pre_trade_date}市值，{pre_trade_date}最终涨停时间，概念
    '''
    print(question)

    # 使用问财获取数据
    df = pywencai.get(question=question)

    # print(df.columns)

    df[f"最终涨停时间[{pre_trade_date}]"] = pd.to_datetime(df[f"最终涨停时间[{pre_trade_date}]"].str.strip(),
                                                           format='%H:%M:%S').dt.time
    time_threshold = pd.to_datetime("11:25:00", format='%H:%M:%S').time()
    df = df[df[f"最终涨停时间[{pre_trade_date}]"] > time_threshold]

    # 排序并重命名列
    df = df.sort_values(by=f"分时涨跌幅:前复权[{query_date} 09:25]", ascending=False)
    df.rename({f"分时涨跌幅:前复权[{query_date} 09:25]": "竞价涨跌幅"}, axis=1, inplace=True)

    df.rename({f"连续涨停天数[{pre_trade_date}]": "连板"}, axis=1, inplace=True)
    df["连板"] = df["连板"].astype(int)

    df.rename({f"成交额[{pre_trade_date}]": "昨日成交额"}, axis=1, inplace=True)
    df.rename({f"竞价金额[{query_date}]": "竞价金额"}, axis=1, inplace=True)
    df["昨日成交额"] = df["昨日成交额"].astype(float)
    df["昨日成交额"] = round(df["昨日成交额"] / 100000000, 2)
    df["竞价金额"] = df["竞价金额"].astype(float)
    df["竞价金额"] = round(df["竞价金额"] / 100000000, 2)

    df.rename({f"a股市值(不含限售股)[{pre_trade_date}]": "市值"}, axis=1, inplace=True)
    df["市值"] = df["市值"].astype(float)
    df["市值"] = round(df["市值"] / 100000000, 2)

    df.rename({"所属同花顺行业": "行业"}, axis=1, inplace=True)

    df["竞价涨跌幅"] = df["竞价涨跌幅"].astype(float)
    df = df.head(10)
    df = df[df["竞价涨跌幅"] > 2]
    # 添加“类型”列，并筛选显示的列
    df["类型"] = "弱板转强"

    df["跌幅"] = 0
    df = df[["股票简称", "股票代码", "竞价涨跌幅", "类型", "连板", "跌幅", "昨日成交额", "竞价金额", "市值", "行业",
             "所属概念"]]

    # 返回最终结果
    return df

def ruoZhuanQiang(query_date):
    df1 = getZhaBan(query_date)
    df2 = getJieLi(query_date)
    df = pd.concat([df1,df2])
    df = df.sort_values(by=["类型","竞价涨跌幅"],ascending=False)
    df_styled = df.style.set_properties(**{'text-align': 'right'})
    pd.set_option('display.max_columns', None)  # 不限制列数显示
    pd.set_option('display.width', 1000)  # 设定一个足够大的值


    return df

def getDxjl(query_date):
    query_date_format = datetime.strptime(query_date, "%Y%m%d").strftime("%Y-%m-%d")
    sql = f'''
        select * from stock.real_market_info_dxjl rmit where 
                trade_type ='逼近涨停' and stock_name not like '%%ST%%'
                and  `timestamp` > '{query_date_format} 09:30:00' and `timestamp`<'{query_date_format} 15:00:00'
                order by `timestamp`  asc
        '''

    # print(sql)
    df_dxjl = pd.read_sql(sql, engine)
    return df_dxjl




def analysisStock(code, query_date_format, query_time,df_dxjl):
    # 将时间字符串转换为 datetime 对象
    time_obj = datetime.strptime(query_time, "%H:%M:%S")

    # 创建表示30秒的 timedelta 对象
    delta = timedelta(seconds=240)

    # 计算前30秒和后30秒的时间
    before_30_seconds = (time_obj - delta).time()
    after_30_seconds = (time_obj + delta).time()

    sql = f'''
        select * from stock.real_market_info_tdx rmit where 
                code = '{code}'
                and  `timestamp` > '{query_date_format} {query_time}' and `timestamp`<'{query_date_format} {after_30_seconds.strftime('%H:%M:%S')}'
                order by `timestamp`  asc
        '''

    # print(sql)
    df_tdx = pd.read_sql(sql, engine)
    df_tdx = pd.merge(df_tdx, df_dxjl[["股票简称","股票代码","连续涨停天数","流通市值","成交额","5日乖离","10日乖离","20日乖离"]], how="left", left_on="code", right_on="股票代码")
    df_tdx["hsl"] = round(df_tdx["amount"] / 10000 / df_tdx["流通市值"] * 100, 2)
    df_tdx["zhsl"] = round(df_tdx["amount"] / 10000 / df_tdx["成交额"] * 100, 2)
    df_tdx["open"] = df_tdx["price"] / (1 + df_tdx["change"] / 100)
    df_tdx["per_price"] = df_tdx["amount"]*100/df_tdx["vol"]
    df_tdx["per_price_change"]=(df_tdx["per_price"]-df_tdx["open"])*100/df_tdx["open"]

    return df_tdx[["timestamp","price","股票简称","code", "change", "amount","per_price_change", "连续涨停天数", "hsl", "zhsl","5日乖离","10日乖离","20日乖离","流通市值","成交额"]]

def get_trading_days(start_date, end_date):
    """
    计算两个日期之间的交易日。
    :param start_date: 开始日期，格式为 'YYYYMMDD'
    :param end_date: 结束日期，格式为 'YYYYMMDD'
    :param trade_dates: 交易日列表
    :return: 交易日数量
    """
    # 将日期字符串转换为 datetime 对象
    start_dt = datetime.strptime(start_date, '%Y%m%d').date()
    end_dt = datetime.strptime(end_date, '%Y%m%d').date()
    trade_dates = ak.tool_trade_date_hist_sina()
    trade_dates = trade_dates['trade_date'].tolist()

    # 过滤出在指定日期范围内的交易日
    filtered_trade_dates = [date for date in trade_dates if start_dt <= date <= end_dt]
    formatted_dates = [date.strftime('%Y%m%d') for date in filtered_trade_dates]
    # 返回交易日数量
    return formatted_dates


def PanZhongJianKong(dxjl_df,panqian_df,query_date):
    logging.info(f"开始进行日内交易")
    dxjl_df = pd.merge(panqian_df, dxjl_df, how="inner", left_on=["股票简称"], right_on=["stock_name"])
    query_date_format = datetime.strptime(query_date, "%Y%m%d").strftime("%Y-%m-%d")
    df_dxjl = dxjl_df.sort_values(by="timestamp", ascending=True)
    stock_list = []
    fail_stock_list = []
    stock_list_set=set()
    desc=""
    for index, row in df_dxjl.iterrows():

        rdf = analysisStock(row["股票代码"], query_date_format, row["trade_time"], df_dxjl)

        if row["股票简称"] in stock_list_set:
            row["desc"] = "已经买入"
            # fail_stock_list.append(row)
            continue

        if row["流通市值"] < 30 and row["成交额"]<1:
            row["desc"] = desc + "|"+ "流通市值小于30亿或者成交额小于1亿"
            fail_stock_list.append(row)
            continue

        if row["连续涨停天数"] > 0:
            row["desc"] =  "连续涨停"
            fail_stock_list.append(row)
            continue

        # if row["20日涨幅"] > 30:
        #     row["desc"] = "20日涨幅大于30"
        #     fail_stock_list.append(row)
        #     continue
        #
        # if row["2日涨幅"] > 10:
        #     row["desc"] = "2日涨幅大于10"
        #     fail_stock_list.append(row)
        #     continue

        is20 = False
        if row["股票代码"].startswith("30") or row["股票代码"].startswith("688"):
            is20 = True
            row["desc"] = "20cm标的"
            fail_stock_list.append(row)



        zhsl_low = 50
        zhsl_high = 110
        change_low = 6

        time_index = 0

        for index, row_r in rdf.iterrows():

            if row_r['timestamp'] < datetime.now().replace(hour=10, minute=0, second=0, microsecond=0):
                zhsl_high = 90
            elif row_r['timestamp'] < datetime.now().replace(hour=11, minute=0, second=0, microsecond=0):
                zhsl_high = 100
            elif row_r['timestamp'] < datetime.now().replace(hour=14, minute=0, second=0, microsecond=0):
                zhsl_high = 110

            if (row_r["change"] > change_low and row_r["change"] < 9.9 and row_r["zhsl"] > zhsl_low and row_r["zhsl"] < zhsl_high
                    and row_r["amount"]>3000 and row_r["per_price_change"]>5.5 and row_r["change"]>row_r["per_price_change"] and
                    (row_r["change"] - row_r["per_price_change"] < 3) and
                    is20 == False):
                stock_list.append(row_r)  # if row["股票简称"] in stock_list:
                stock_list_set.add(row_r["股票简称"])
                break

            if row_r["zhsl"]>zhsl_high:
                row["desc"] = "自由换手太大"+str(row_r)
                fail_stock_list.append(row)
                break

            if row_r["zhsl"]<zhsl_low and time_index>200:
                row["desc"] = "自由换手太小"+str(row_r)
                fail_stock_list.append(row)
                break

            time_index = time_index + 1

    return pd.DataFrame(stock_list),pd.DataFrame(fail_stock_list)

def analysisByDate(query_date):
    qingxu_df = QingXu(query_date)
    ruozhuanqiang_df = ruoZhuanQiang(query_date)
    panqian_df = r.get(f"stock_panqian:{query_date}")
    if panqian_df is None:
        print("取新的复盘数据")
        panqian_df = PanQian(query_date)
    else:
        panqian_df = pickle.loads(panqian_df)

    dxjl_df = getDxjl(query_date)
    shouban_df,fail_shouban_df = PanZhongJianKong(dxjl_df,panqian_df,query_date)
    print(shouban_df)

    file_path = f"分析结果/{query_date}.xlsx"
    with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
        # 分别将DataFrame写入不同的sheet
        qingxu_df.to_excel(writer, sheet_name='情绪', index=False)
        ruozhuanqiang_df.to_excel(writer, sheet_name='弱转强', index=False)
        shouban_df.to_excel(writer, sheet_name='首板', index=False)
        fail_shouban_df.to_excel(writer, sheet_name='首板失败', index=False)


if __name__ == '__main__':
    #检查是否有命令行参数（除了脚本名称本身）
    import akshare as ak
    date_df = ak.tool_trade_date_hist_sina()
    date_df['trade_date'] = pd.to_datetime(date_df['trade_date'])

    # 筛选出2024年4月的交易日
    april_2024 = date_df[(date_df['trade_date'] >= '2024-03-11') & (date_df['trade_date'] <= '2024-05-31')]
    april_2024['trade_date_str'] = april_2024['trade_date'].dt.strftime('%Y%m%d')

    try:
        for t in april_2024["trade_date_str"].values:
            PanQian(t)
    #捕获异常
    except Exception as e:
        logging.error(f"Error occurred: {e}")
        sys.exit(1)



    # date_df = get_trading_days("20240313","20240401")
    # for v in date_df:
    #     PanQian(v)


